﻿/* 
This file is part of Ingo Karstein's Excel Export project

**Do not remove this comment**

Please see the project homepage at CodePlex:
  http://spexcelexport.codeplex.com/

Please see my blog:
  http://ikarstein.wordpress.com

Licence: GPLv2
    Ingo Karstein's SharePoint 2010 Custom Ribbon Demo
    Copyright (C) 2011  Ingo Karstein, ikarstein@hotmail.com

    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License along
    with this program; if not, write to the Free Software Foundation, Inc.,
    51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using System.IO.Packaging;
using System.Xml;
using System.Globalization;
using System.Diagnostics;
using Microsoft.SharePoint;
using System.Web.UI;
using System.Xml.Linq;
using System.Xml.XPath;

namespace ik.SharePoint2010.ExcelExport.Code
{
    class ExportToXlsx
    {
        private class FieldInfo
        {
            public string internalName;
            public string displayName;
            public int lcid = CultureInfo.CurrentUICulture.LCID;
            public string type;
        }

        private class StyleInfo
        {
            public string styleType = "";
            public string styleId = "";
        }

        private class OrderData
        {
            public string Key;
            public string FieldInternalName;
            public SortedList<object, OrderData> Data = null;

            public OrderData(string key, string fieldInternalName, SortedList<object, OrderData> data)
            {
                this.Key = key;
                this.FieldInternalName = fieldInternalName;
                this.Data = data;
            }
        }

        private static object ConvertFieldValue(string val, string type, int lcid)
        {
            CultureInfo webci = new CultureInfo(lcid);

            string data = val;

            switch( type.ToLower() )
            {
                default:
                    return data;
                case "multiline":
                case "text":
                    return val;
                    break;
                case "integer":
                    {
                        int i;
                        if( int.TryParse(data, out i) )
                            return i;
                        else
                            return data;
                    }
                    break;
                case "currency":
                    {
                        float d = 0;

                        if( float.TryParse(data, NumberStyles.Any | NumberStyles.AllowCurrencySymbol | NumberStyles.AllowThousands |
                                                   NumberStyles.AllowDecimalPoint, webci.NumberFormat, out d) )
                        {
                            return d;
                            break;
                        }
                        else
                            foreach( CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures) )
                                if( !ci.IsNeutralCulture )
                                    if( float.TryParse(data, NumberStyles.Currency, ci.NumberFormat, out d) )
                                    {
                                        return d;
                                        break;
                                    }

                        return data;
                    };
                    break;
                case "datetime":
                    {
                        DateTime dt = DateTime.MinValue;

                        if( DateTime.TryParse(data, webci.DateTimeFormat, DateTimeStyles.None, out dt) )
                        {
                            return dt;
                            break;
                        }
                        else
                            foreach( CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures) )
                                if( !ci.IsNeutralCulture )
                                    if( DateTime.TryParse(data, ci.DateTimeFormat, DateTimeStyles.None, out dt) )
                                    {
                                        return dt;
                                        break;
                                    }

                        return data;
                    };
                    break;
                case "lookup":
                    {
                        if( data.IndexOf(";#") >= 0 )
                            data = data.Substring(data.IndexOf(";#") + 2);
                        return data;
                    }
                    break;
                case "number":
                    {
                        float d = 0;
                        if( float.TryParse(data, NumberStyles.Float, webci.NumberFormat, out d) )
                        {
                            return d;
                            break;
                        }
                        else
                            foreach( CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures) )
                                if( !ci.IsNeutralCulture )
                                    if( float.TryParse(data, NumberStyles.Float, ci.NumberFormat, out d) )
                                    {
                                        return d;
                                        break;
                                    }
                        return data;
                    }
                    break;
            }
        }

        private static string ConvertToString(object val, string type, int lcid)
        {
            CultureInfo webci = new CultureInfo(lcid);

            switch( type.ToLower() )
            {
                default:
                    return val.ToString();
                case "multiline":
                case "text":
                    return val.ToString();
                    break;
                case "integer":
                    return ( (int)val ).ToString();
                    break;
                case "currency":
                    return ( (double)val ).ToString("C", webci);
                    break;
                case "datetime":
                    return ( (DateTime)val ).ToString(webci);
                    break;
                case "lookup":
                    return val.ToString();
                    break;
                case "number":
                    return ( (float)val ).ToString(webci);
                    break;
            }
        }

        private static void AddPartFromEmbeddedResource(ref Package p, string path, string embeddedResourceName, string contentType)
        {
            Assembly r = Assembly.GetExecutingAssembly();

            PackagePart pp = p.CreatePart(new Uri(path, UriKind.Relative), contentType, CompressionOption.Maximum);
            Stream outp = pp.GetStream();

            int readBytes = 0;
            byte[] buf = new byte[1000000];
            Stream inp = r.GetManifestResourceStream(embeddedResourceName);
            do
            {
                readBytes = inp.Read(buf, 0, buf.Length);
                outp.Write(buf, 0, readBytes);
            } while( readBytes > 0 );
            inp.Close();

            outp.Flush();
            outp.Close();
        }

        private static void AddPartFromString(ref Package p, string path, string partStr, string contentType)
        {
            Assembly r = Assembly.GetExecutingAssembly();

            PackagePart pp = p.CreatePart(new Uri(path, UriKind.Relative), contentType, CompressionOption.Maximum);
            Stream outp = pp.GetStream();

            int readBytes = 0;
            byte[] buf = new byte[1000000];
            Stream inp = new MemoryStream(Encoding.UTF8.GetBytes(partStr));
            do
            {
                readBytes = inp.Read(buf, 0, buf.Length);
                outp.Write(buf, 0, readBytes);
            } while( readBytes > 0 );
            inp.Close();

            outp.Flush();
            outp.Close();
        }

        private static string ReadStringFromStream(Stream inp)
        {
            MemoryStream outp = new MemoryStream();

            int readBytes = 0;
            byte[] buf = new byte[1000000];
            do
            {
                readBytes = inp.Read(buf, 0, buf.Length);
                outp.Write(buf, 0, readBytes);
            } while( readBytes > 0 );
            inp.Close();

            outp.Flush();

            byte[] data = outp.ToArray();

            outp.Close();

            return Encoding.UTF8.GetString(data);
        }



        private static SortedList<object, OrderData> FillRowValues(SortedList<string, FieldInfo> fieldInfos, SortedList<int, string> rowfields, int rowfieldidx, XElement selection)
        {
            if( rowfieldidx >= rowfields.Count )
                return null;

            SortedList<object, OrderData> ret = new SortedList<object, OrderData>();

            string rf = rowfields[rowfieldidx];

            IEnumerable<XElement> x2 = from xe in selection.XPathSelectElements("//field[@internalName=\"" + rf + "\"]")
                                       select xe;

            foreach( XElement x2e in x2 )
            {
                string oKey = x2e.Value;
                object key = ConvertFieldValue(oKey, fieldInfos[rf].type, fieldInfos[rf].lcid);

                if( !ret.ContainsKey(key) )
                {
                    var x3 = new XElement("rows", from xe in selection.XPathSelectElements("//row")
                                                  where xe.XPathSelectElement("field[@internalName=\"" + rf + "\"]").Value == oKey
                                                  select xe);

                    SortedList<object, OrderData> subret = FillRowValues(fieldInfos, rowfields, rowfieldidx + 1, x3);
                    ret.Add(key, new OrderData(oKey, rf, subret));
                };
            }

            return ret;
        }

        private static SortedList<object, OrderData> StartFillRowValues(SortedList<string, FieldInfo> fieldInfos, SortedList<int, string> rowfields, XElement xmlData)
        {
            string s = rowfields[0];
            var x1 = new XElement("rows", from xe in xmlData.Element("rows").Elements("row")
                                          where xe.XPathSelectElement("field[@internalName=\"" + s + "\"]") != null
                                          select xe);

            return FillRowValues(fieldInfos, rowfields, 0, x1);
        }

        private static IEnumerable<XElement> OutputData(XElement rows, SortedList<object, OrderData> order, SortedList<string, FieldInfo> fieldInfos, int idx, XElement addFields)
        {
            XElement container = new XElement("rows");

            foreach( KeyValuePair<object, OrderData> orderItem in order )
            {
                SortedList<object, OrderData> data = orderItem.Value.Data;
                string internalName = orderItem.Value.FieldInternalName;
                string key = orderItem.Value.Key;

                var x3 = new XElement("rows", from xe in rows.XPathSelectElements("//row")
                                              where xe.XPathSelectElement("field[@internalName=\"" + internalName + "\"]").Value == key
                                              select xe);

                x3.XPathSelectElements("//field[@internalName=\"" + internalName + "\"]").Remove();

                var newRow = new XElement("row");
                if( addFields != null )
                    newRow.Add(addFields.XPathSelectElements("//field"));

                newRow.Add(new XElement("field", new XAttribute("internalName", internalName), new XText(key)));

                if( data != null )
                {
                    container.Add(OutputData(x3, data, fieldInfos, idx + 1, newRow));
                }
                else
                {
                    newRow.Add(x3.XPathSelectElements("//field"));
                    container.Add(newRow);
                }
            };

            return container.XPathSelectElements("//row");
        }

        private static StyleInfo GetFormat(ref SortedList<string, StyleInfo> styles, bool IsHeadline, bool IsRow, string type, int lcid)
        {
            StyleInfo ret = new StyleInfo();
            ret.styleId = "";
            ret.styleType = "s";

            if( styles.ContainsKey("DefaultFormat") )
                ret = styles["DefaultFormat"];

            string stylename = "";

            if( IsHeadline )
            {
                stylename += "Headline";
                if( styles.ContainsKey(stylename) )
                    ret = styles[stylename];
            }
            else if( IsRow )
            {
                stylename += "Row";
                if( styles.ContainsKey(stylename) )
                    ret = styles[stylename];
            }
            else
            {
                stylename += "Cell";
                if( styles.ContainsKey(stylename) )
                    ret = styles[stylename];
            }

            switch( type )
            {
                case "integer":
                    stylename += "Integer";
                    break;
                case "number":
                    stylename += "Number";
                    break;
                case "currency":
                    stylename += "Currency";
                    break;
                case "datetime":
                    stylename += "DateTime";
                    break;
                case "multiline":
                    stylename += "Multiline";
                    break;
                case "lookup":
                    stylename += "Lookup";
                    break;
            };

            if( styles.ContainsKey(stylename) )
                ret = styles[stylename];

            stylename += lcid.ToString();

            if( styles.ContainsKey(stylename) )
                ret = styles[stylename];

            return ret;
        }


        private static void ReorderFields(ref XElement rows, SortedList<int, FieldInfo> fields)
        {
            XElement newRows = new XElement("rows");
            foreach( var row in rows.XPathSelectElements("row") )
            {
                XElement newRow = new XElement("row");

                foreach( var field in fields )
                {
                    string internalName = field.Value.internalName;
                    IEnumerable<XElement> fieldlist = row.XPathSelectElements("field[@internalName=\"" + internalName + "\"]");
                    newRow.Add(fieldlist);
                };

                newRows.Add(newRow);
            };
            rows.ReplaceWith(newRows);
        }

        private static void ResortHeadlines(ref XElement newRows, ref SortedList<int, string> resortedHeadlines)
        {
            foreach( var row in newRows.XPathSelectElements("row") )
            {
                int idx = 0;
                foreach( var col in row.XPathSelectElements("field") )
                {
                    do
                    {
                        if( idx >= resortedHeadlines.Count )
                        {
                            resortedHeadlines.Add(idx, col.Attribute("internalName").Value);
                            col.Add(new XAttribute("idx", idx));
                            break;
                        }
                        else
                        {
                            if( resortedHeadlines[idx] != col.Attribute("internalName").Value )
                                idx++;
                            else
                            {
                                col.Add(new XAttribute("idx", idx));
                                break;
                            };
                        };
                    } while( true );
                };
            };
        }

        private static string GetCellEncoded(int row, int col)
        {
            int encCellCol = col;
            string encCellColStr = "";
            string encBase = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            do
            {
                encCellColStr = encBase.Substring(( encCellCol - 1 ) % ( encBase.Length ), 1) + encCellColStr;
                encCellCol /= encBase.Length;
            } while( encCellCol > 0 );

            return encCellColStr + row.ToString();
        }
    

        public static byte[] Export(string xmlData)
        {
            //Debugger.Break();

            SPWeb web = SPContext.Current.Web;
            SPList templList = null;
            if( web != null )
                templList = web.GetList(web.ServerRelativeUrl + "/Lists/ExcelExportTemplates");

            SPQuery q = new SPQuery();
            q.Query = @"<Query><Where><Eq><FieldRef Name=""Title"" /><Value Type=""Text"">template</Value></Eq></Where></Query>";

            SPListItem templItem = null;
            SPListItemCollection templItems = templList.GetItems(q);
            if( templItems.Count >= 1 )
                templItem = templItems[0];

            SPFile f = web.GetFile(templItem.Url);
            Stream templStream = f.OpenBinaryStream(SPOpenBinaryOptions.SkipVirusScan);

            // <-- Styles begin

            Package templ = Package.Open(templStream);
            PackagePart templSheet1 = templ.GetPart(new Uri("/xl/worksheets/sheet1.xml", UriKind.Relative));
            PackagePart templStrings = templ.GetPart(new Uri("/xl/sharedStrings.xml", UriKind.Relative));
            PackagePart templStyles = templ.GetPart(new Uri("/xl/styles.xml", UriKind.Relative));

            string templSheet1Str = ReadStringFromStream(templSheet1.GetStream());
            string templStylesStr = ReadStringFromStream(templStyles.GetStream());
            string templStringsStr = ReadStringFromStream(templStrings.GetStream());
            templ.Close();
            templStream.Close();

            XmlDocument templStringsXml = new XmlDocument();

            XmlNamespaceManager nsm1 = new XmlNamespaceManager(templStringsXml.NameTable);
            nsm1.AddNamespace("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

            SortedList<int, string> sharedStrings = new SortedList<int, string>();
            templStringsXml.LoadXml(templStringsStr);
            foreach( XmlElement s in templStringsXml.DocumentElement.SelectNodes("x:si", nsm1) )
            {
                sharedStrings.Add(sharedStrings.Count, s.SelectSingleNode("x:t", nsm1).InnerText);
            }

            XmlDocument templSheet1Xml = new XmlDocument();
            templSheet1Xml.LoadXml(templSheet1Str);

            XmlNamespaceManager nsm2 = new XmlNamespaceManager(templSheet1Xml.NameTable);
            nsm2.AddNamespace("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
            nsm2.AddNamespace("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            nsm2.AddNamespace("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            nsm2.AddNamespace("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

            SortedList<string, StyleInfo> styles = new SortedList<string, StyleInfo>();

            XmlNodeList templRows = templSheet1Xml.SelectNodes("/x:worksheet/x:sheetData/x:row", nsm2);
            foreach( XmlNode templRow in templRows )
            {
                SortedList<string, XmlNode> cols = new SortedList<string, XmlNode>();
                foreach( XmlNode templCol in templRow.SelectNodes("x:c", nsm2) )
                {
                    cols.Add(templCol.Attributes["r"].Value, templCol);
                };

                string nameCodeStr = cols[cols.Keys[0]].SelectSingleNode("x:v", nsm2).InnerText;
                int nameCode = int.Parse(nameCodeStr);
                string name = sharedStrings[nameCode];

                StyleInfo s = new StyleInfo();

                s.styleId = ( cols[cols.Keys[1]].Attributes["s"] != null ? cols[cols.Keys[1]].Attributes["s"].Value : "" );
                s.styleType = ( cols[cols.Keys[1]].Attributes["t"] != null ? cols[cols.Keys[1]].Attributes["t"].Value : "" );
                styles.Add(name, s);
            }

            // --> Styles end


            MemoryStream ms = new MemoryStream();
            Package p = Package.Open(ms, FileMode.Create);

            AddPartFromEmbeddedResource(ref p, "/_rels/.rels", "ik.SharePoint2010.ExcelExport.TemplateXLSX._rels..rels", "application/vnd.openxmlformats-package.relationships+xml");

            AddPartFromEmbeddedResource(ref p, "/docProps/app.xml", "ik.SharePoint2010.ExcelExport.TemplateXLSX.docProps.app.xml", "application/vnd.openxmlformats-officedocument.extended-properties+xml");
            AddPartFromEmbeddedResource(ref p, "/docProps/core.xml", "ik.SharePoint2010.ExcelExport.TemplateXLSX.docProps.core.xml", "application/vnd.openxmlformats-package.core-properties+xml");

            AddPartFromEmbeddedResource(ref p, "/xl/_rels/workbook.xml.rels", "ik.SharePoint2010.ExcelExport.TemplateXLSX.xl._rels.workbook.xml.rels", "application/vnd.openxmlformats-package.relationships+xml");

            AddPartFromEmbeddedResource(ref p, "/xl/printerSettings/printerSettings1.bin", "ik.SharePoint2010.ExcelExport.TemplateXLSX.xl.printerSettings.printerSettings1.bin", "application/vnd.openxmlformats-officedocument.spreadsheetml.printerSettings");

            //AddPartFromEmbeddedResource(ref p, "/xl/theme/theme1.xml", "ik.SharePoint2010.ExcelExport.TemplateXLSX.xl.theme.theme1.xml", "application/vnd.openxmlformats-officedocument.theme+xml");

            AddPartFromEmbeddedResource(ref p, "/xl/worksheets/_rels/sheet1.xml.rels", "ik.SharePoint2010.ExcelExport.TemplateXLSX.xl.worksheets._rels.sheet1.xml.rels", "application/vnd.openxmlformats-package.relationships+xml");
            //AddPartFromEmbeddedResource(ref p, "/xl/worksheets/sheet1.xml", "ik.SharePoint2010.ExcelExport.TemplateXLSX.xl.worksheets.sheet1.xml", "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");
            //AddPartFromEmbeddedResource(ref p, "/xl/worksheets/sheet2.xml", "ik.SharePoint2010.ExcelExport.TemplateXLSX.xl.worksheets.sheet2.xml", "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");
            //AddPartFromEmbeddedResource(ref p, "/xl/worksheets/sheet3.xml", "ik.SharePoint2010.ExcelExport.TemplateXLSX.xl.worksheets.sheet3.xml", "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");

            //AddPartFromEmbeddedResource(ref p, "/xl/sharedStrings.xml", "ik.SharePoint2010.ExcelExport.TemplateXLSX.xl.sharedStrings.xml", "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml");
            //AddPartFromEmbeddedResource(ref p, "/xl/styles.xml", "ik.SharePoint2010.ExcelExport.TemplateXLSX.xl.styles.xml", "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml");
            AddPartFromString(ref p, "/xl/styles.xml", templStylesStr, "application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml");
            AddPartFromEmbeddedResource(ref p, "/xl/workbook.xml", "ik.SharePoint2010.ExcelExport.TemplateXLSX.xl.workbook.xml", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml");

            XElement splistLinq = XElement.Parse(xmlData);

            Assembly r = Assembly.GetExecutingAssembly();

            XmlDocument sheet = new XmlDocument();
            TextReader tr = new StreamReader(r.GetManifestResourceStream("ik.SharePoint2010.ExcelExport.Code.SheetTemplate.xml"));
            sheet.LoadXml(tr.ReadToEnd());
            tr.Close();

            // --> Copy from

            //create field infos

            SortedList<int, FieldInfo> fieldInfos = new SortedList<int, FieldInfo>();
            SortedList<string, FieldInfo> fieldInfos2 = new SortedList<string, FieldInfo>();

            IEnumerable<XElement> fields = splistLinq.Element("fields").Elements("field");

            foreach( XElement n in fields )
            {
                FieldInfo fi = new FieldInfo();
                fi.displayName = n.Attribute("displayName").Value;
                fi.internalName = n.Attribute("internalName").Value;
                if( n.Attribute("lcid") != null && !string.IsNullOrEmpty(n.Attribute("lcid").Value) )
                    fi.lcid = int.Parse(n.Attribute("lcid").Value);
                fi.type = n.Attribute("type").Value;

                fieldInfos.Add(fieldInfos.Count, fi);
                fieldInfos2.Add(fi.internalName, fi);
            };

            //reorder fields in row
            XElement rows1 = splistLinq.Element("rows");
            ReorderFields(ref rows1, fieldInfos);

            //create row fields

            IEnumerable<XElement> rowfields = splistLinq.Element("rowfields").Elements("field");
            SortedList<int, string> rowfieldlist = new SortedList<int, string>();

            if( rowfields.Count() > 0 )
            {
                foreach( XElement n in rowfields )
                {
                    rowfieldlist.Add(rowfieldlist.Count, n.Attribute("internalName").Value);
                }

                SortedList<object, OrderData> sortedRowValues = StartFillRowValues(fieldInfos2, rowfieldlist, splistLinq);
                XElement newRows = new XElement("rows", OutputData(splistLinq, sortedRowValues, fieldInfos2, 0, null));

                splistLinq.Element("rows").ReplaceWith(newRows);
            };


            SortedList<int, string> resortedHeadlines = new SortedList<int, string>();

            XElement rowsResort = splistLinq.Element("rows");
            ResortHeadlines(ref rowsResort, ref resortedHeadlines);
            splistLinq.Element("rows").ReplaceWith(rowsResort);


            StringTable st = new StringTable();

            XmlNode headlines = sheet.CreateElement("row", Namespaces.SpreadsheetML);
            XmlNode sheetDataNode = sheet.DocumentElement["sheetData"];
            sheetDataNode.AppendChild(headlines);

            int columns = resortedHeadlines.Count, rows = 0;

            //output headlines
            int countCols = 0;
            foreach( string headline in resortedHeadlines.Values )
            {
                countCols++;

                StyleInfo nFormat = GetFormat(ref styles, true, false, "text", 0);

                XmlNode h = sheet.CreateElement("c", Namespaces.SpreadsheetML);
                XmlAttribute t = sheet.CreateAttribute("t");
                t.Value = nFormat.styleType;
                h.Attributes.Append(t);

                XmlAttribute rangeAttr = sheet.CreateAttribute("r");
                rangeAttr.Value = GetCellEncoded(1, countCols);
                h.Attributes.Append(rangeAttr);

                XmlAttribute styleAttr = sheet.CreateAttribute("s");
                styleAttr.Value = nFormat.styleId;
                h.Attributes.Append(styleAttr);

                XmlNode v = sheet.CreateElement("v", Namespaces.SpreadsheetML);

                if( nFormat.styleType == "s" )
                    v.InnerText = st.GetId(fieldInfos2[headline].displayName).ToString();
                else
                    v.InnerText = fieldInfos2[headline].displayName;

                h.AppendChild(v);

                headlines.AppendChild(h);
            }

            rows = 1;

            //sortedRowValues = FillRowValues(rowfieldlist, 0, x, "");

            IEnumerable<XElement> rowsNL = splistLinq.Element("rows").Elements("row");
            foreach( XElement n in rowsNL )
            {
                rows++;

                XmlNode resultRow = sheet.CreateElement("row", Namespaces.SpreadsheetML);
                sheetDataNode.AppendChild(resultRow);

                int lastIdx = -1;
                int countCols2 = 0;
                foreach( XElement f2 in n.Elements("field") )
                {
                    countCols2++;

                    int idx = int.Parse(f2.Attribute("idx").Value);

                    while( idx - lastIdx > 1 )
                    {
                        //Dummy column
                        /*
                        XmlNode c = sheet.CreateElement("c", Namespaces.SpreadsheetML);

                        XmlNode v = sheet.CreateElement("v", Namespaces.SpreadsheetML);

                        resultRow.AppendChild(c);
                        */

                        countCols2++;

                        lastIdx++;
                    }

                    lastIdx = idx;

                    FieldInfo fi = fieldInfos2[f2.Attribute("internalName").Value];

                    CultureInfo webci = new CultureInfo(fi.lcid);

                    string data = f2.Value;

                    bool isString = false;
                    int format = 0;

                    StyleInfo nFormat = GetFormat(ref styles, false, rowfieldlist.ContainsValue(fi.internalName), fi.type.ToLower(), webci.LCID);

                    switch( fi.type.ToLower() )
                    {
                        case "multiline":
                            isString = true;
                            format = 4;
                            break;
                        case "text":
                            isString = true;
                            break;
                        case "integer":
                            nFormat.styleType = "";
                            break;
                        case "currency":
                            {
                                float d = 0;
                                bool converted = false;
                                if( float.TryParse(data, NumberStyles.Any | NumberStyles.AllowCurrencySymbol | NumberStyles.AllowThousands |
                                                           NumberStyles.AllowDecimalPoint, webci.NumberFormat, out d) )
                                {
                                    converted = true;
                                }
                                else
                                    foreach( CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures) )
                                        if( !ci.IsNeutralCulture )
                                            if( float.TryParse(data, NumberStyles.Currency, ci.NumberFormat, out d) )
                                            {
                                                converted = true;
                                                break;
                                            }
                                if( !converted )
                                    isString = true;
                                else
                                {
                                    data = d.ToString(CultureInfo.InvariantCulture.NumberFormat);
                                    nFormat.styleType = "";
                                };
                            };
                            break;
                        case "datetime":
                            {
                                DateTime dt = DateTime.MinValue;
                                bool converted = false;
                                if( DateTime.TryParse(data, webci.DateTimeFormat, DateTimeStyles.None, out dt) )
                                {
                                    converted = true;
                                }
                                else
                                    foreach( CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures) )
                                        if( !ci.IsNeutralCulture )
                                            if( DateTime.TryParse(data, ci.DateTimeFormat, DateTimeStyles.None, out dt) )
                                            {
                                                converted = true;
                                                break;
                                            }
                                if( !converted )
                                    isString = true;
                                else
                                {
                                    TimeSpan ts = dt.Subtract(new DateTime(1899, 12, 31, 0, 0, 0));
                                    double dtf = ts.TotalDays;
                                    data = dtf.ToString(CultureInfo.InvariantCulture.NumberFormat);
                                    nFormat.styleType = "";
                                }
                            };
                            break;
                        case "lookup":
                            {
                                isString = true;
                                if( data.IndexOf(";#") >= 0 )
                                    data = data.Substring(data.IndexOf(";#") + 2);
                            }
                            break;
                        case "number":
                            {
                                float d = 0;
                                bool converted = false;
                                if( float.TryParse(data, NumberStyles.Float, webci.NumberFormat, out d) )
                                {
                                    converted = true;
                                }
                                else
                                    foreach( CultureInfo ci in CultureInfo.GetCultures(CultureTypes.AllCultures) )
                                        if( !ci.IsNeutralCulture )
                                            if( float.TryParse(data, NumberStyles.Float, ci.NumberFormat, out d) )
                                            {
                                                converted = true;
                                                break;
                                            }
                                if( !converted )
                                    isString = true;
                                else
                                {
                                    data = d.ToString(CultureInfo.InvariantCulture);
                                    nFormat.styleType = "";
                                };
                            }
                            break;
                    }

                    if( string.IsNullOrEmpty(data) )
                        continue;


                    XmlNode cNode = sheet.CreateElement("c", Namespaces.SpreadsheetML);

                    XmlAttribute rangeAttr = sheet.CreateAttribute("r");
                    rangeAttr.Value = GetCellEncoded(rows, countCols2);
                    cNode.Attributes.Append(rangeAttr);

                    if( !( string.IsNullOrEmpty(nFormat.styleId) ) )
                    {
                        XmlAttribute ts = sheet.CreateAttribute("s");
                        ts.Value = nFormat.styleId;
                        cNode.Attributes.Append(ts);
                    };

                    XmlNode vNode = sheet.CreateElement("v", Namespaces.SpreadsheetML);
                    cNode.AppendChild(vNode);

                    if( isString )
                    {
                        XmlAttribute t = sheet.CreateAttribute("t");
                        t.Value = "s";
                        cNode.Attributes.Append(t);
                        vNode.InnerText = st.GetId(data).ToString();
                    }
                    else
                    {
                        vNode.InnerText = data;
                    }


                    resultRow.AppendChild(cNode);

                }
            }

            XmlNode dimensionNode = sheet.DocumentElement["dimension"];
            dimensionNode.Attributes["ref"].Value = "A1:" + GetCellEncoded(rows, columns);

            AddPartFromString(ref p, "/xl/sharedStrings.xml", st.ToXmlString(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml");
            AddPartFromString(ref p, "/xl/worksheets/sheet1.xml", sheet.OuterXml, "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml");

            p.Flush();
            p.Close();
            ms.Flush();

            // <-- Copy To

            byte[] retData = ms.ToArray();
            ms.Close();

            return retData;
        }
    }
}
